from odoo import fields, models


class ProductionMaterialDemand(models.Model):
    _name = 's.production.material.demand'
    _description = 'Production Material Demand'
    _auto = False

    product_id = fields.Many2one('product.product', string='Product')
    default_code = fields.Char(related='product_id.default_code', string='Code')
    qty = fields.Float('Quantity', digits='Product Unit of Measure')
    stock_qty = fields.Float('Stock Quantity', digits='Product Unit of Measure', related='product_id.s_actual_quantity')
    before_production_qty = fields.Float('Before Production Qty', digits='Product Unit of Measure')
    difference = fields.Float('Difference', digits='Product Unit of Measure')
    uom_id = fields.Many2one('uom.uom', string='Uom')

    def action_picking(self):
        return {
            'name': '',
            'type': 'ir.actions.act_window',
            'view_mode': 'form',
            'res_model': 's.production.material.demand.wizard',
            # 'domain': [('id', 'in', records.ids)],
            'view_id': self.env.ref('pcbmanufact.s_production_material_demand_wizard_form_view').id,
            'target': 'new',

        }

    def open_lines(self):
        self.ensure_one()
        return {
            'name': '',
            'type': 'ir.actions.act_window',
            'res_model': 's.production.material.demand.line',
            'view_mode': 'tree',
            'target': 'new',
            'context': {
                'material_product_id': self.product_id.id
            }
        }

    @property
    def _table_query(self):
        company_id = self.env.user.company_id
        return f"""
            WITH RECURSIVE sub_locations AS (
                SELECT
                    id,
                    location_id,
                    barcode
                FROM stock_location
                WHERE barcode = 'WH-PREPRODUCTION'
                UNION ALL
                -- 递归查询：找到所有子库位
                SELECT
                    sl.id,
                    sl.location_id,
                    sl.barcode
                FROM stock_location sl
                INNER JOIN sub_locations sl_prev ON sl.location_id = sl_prev.id
            ),
            quants AS (
                SELECT
                    sq.product_id AS product_id,
                    SUM(sq.quantity) AS quantity,
                    pt.uom_id AS uom_id
                FROM
                    stock_quant sq
                LEFT JOIN product_product p ON sq.product_id = p.id
                LEFT JOIN product_template pt ON p.product_tmpl_id = pt.id
                INNER JOIN sub_locations s ON sq.location_id = s.id  
                WHERE sq.company_id = {company_id.id}
                GROUP BY sq.product_id, pt.uom_id
            ),
            moves AS (
                SELECT
                    sm.product_id,
                    SUM(sm.product_qty) AS qty,
                    sm.product_uom
                FROM
                    stock_move sm
                LEFT JOIN product_product pp ON sm.product_id = pp.id
                LEFT JOIN product_template pt ON pp.product_tmpl_id = pt.id
                LEFT JOIN uom_uom uu ON sm.product_uom = uu.id
                LEFT JOIN stock_location sl2 ON sm.location_dest_id = sl2.id
                LEFT JOIN product_category pc ON pt.categ_id = pc.id
                LEFT JOIN product_category pc2 ON pc2.id = pc.parent_id
                WHERE
                    sl2.usage = 'production'
                    AND sm.state NOT IN ('done', 'cancel')
                    AND pc.code NOT IN ('CHP', 'FYL', 'BCP')   -- 排除子类
                    AND (pc2.code IS NULL OR pc2.code != 'CR')
                    AND sm.company_id = {company_id.id}
                GROUP BY
                    sm.product_id, sm.product_uom
            )
            -- 主查询：计算差异
            SELECT
                moves.product_id AS id,
                moves.product_id,
                moves.qty AS qty,
                COALESCE(quants.quantity, 0) AS before_production_qty,
                moves.qty - COALESCE(quants.quantity, 0) AS difference,
                moves.product_uom AS uom_id
            FROM
                moves
            LEFT JOIN quants ON moves.product_id = quants.product_id AND moves.product_uom = quants.uom_id
            WHERE round(moves.qty - COALESCE(quants.quantity, 0), 3) > 0
        
        """
